--SET XACT_ABORT ON
SET NOCOUNT ON
DROP TABLE IF EXISTS testtbl
CREATE TABLE testtbl 
    (id      int     NOT NULL,
     datecol date    NOT NULL,
     xmlcol  xml     NULL,
     CONSTRAINT pk_testtbl PRIMARY KEY (id))
go
CREATE OR ALTER PROCEDURE inner_sp AS
BEGIN TRY
   PRINT '---inner_sp starting'
   BEGIN TRANSACTION

   INSERT testtbl(id, datecol) VALUES (11, '2001-01-01')
   --; THROW 57000, 'I am throwing an error', 77
   -- RAISERROR('I am raising an error', 16, 1)
   -- UPDATE testtbl SET xmlcol = (SELECT query_plan FROM sys.dm_exec_query_statistics_xml(@@spid))
   -- RAISERROR('I am raising a severe error', 20, 1) WITH LOG
   -- WAITFOR DELAY '00:01:00'
   --INSERT nosuchtbl(id, datecol)
   INSERT testtbl(id, datecol)
      VALUES (12, '2002-02-02'), 
             (13, NULL), 
             --(13, 'Bad date'),
             (14, '2004-04-04')

   INSERT testtbl(id, datecol) VALUES (15, '2005-05-05')

   COMMIT TRANSACTION
   PRINT '---inner_sp completed without error'
END TRY
BEGIN CATCH
   PRINT '---inner_sp RAISED ERROR: ' + error_message()
   COMMIT TRANSACTION
   PRINT '---inner_sp exiting CATCH handler.'
END CATCH
go
EXEC inner_sp 
PRINT 'After inner_sp'
go
CREATE OR ALTER PROCEDURE outer_sp AS
BEGIN TRY
   PRINT 'outer_sp starting'
   EXEC inner_sp
   PRINT 'outer_sp completed without incidents'
END TRY
BEGIN CATCH
   PRINT 'outer_sp RAISED ERROR: ' + error_message()
   COMMIT TRANSACTION
   PRINT 'outer_sp exiting CATCH handler.'
END CATCH
go
--EXEC outer_sp
go
SELECT id, datecol FROM testtbl ORDER BY id
IF @@trancount > 0 
BEGIN
   PRINT 'AFTER THE TEST, THERE IS AN ACTIVE TRANSACTION!'
   ROLLBACK TRANSACTION
END
ELSE 
   PRINT 'No transaction is active after the test'
